﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Automobile.service.Common;
using Automobile.service.Model;
using System.Data.SqlClient;
using System.Data;
using System.Web.Script.Serialization;
namespace Automobile.service.DAL
{
  public class ProductDAL
    {
      SQLHelper helper = new SQLHelper();
        /// <summary>
        /// 获取商品信息
        /// </summary>
        /// <returns></returns>
        public List<Product> ShowProduc()
        {
            StringBuilder bulider = new StringBuilder("select Product_Id,Product_Name,Product_Modelspe,Product_Bprid,Product_Price,Product_Type_Name,Product_Barcode,Supplier_Name,Product_Remark from Product join Product_Type on Product.Product_Type_Id=Product_Type.Product_Type_Id join Supplier on Supplier.Supplier_Id=Product.Supplier_Id");
            DataTable table = helper.ExecDataTable(bulider.ToString());
            List<Product> list = new List<Product>();
            foreach (DataRow item in table.Rows)
            {
                Product p = new Product();
                p.Product_Id = Convert.ToInt32(item["Product_Id"]);
                p.Product_Name = item["Product_Name"].ToString();
                p.Product_Modelspe = item["Product_Modelspe"].ToString();
                p.Product_Bprid = Convert.ToInt32(item["Product_Bprid"]);
                p.Product_Price = Convert.ToInt32(item["Product_Price"]);
                p.Product_Type_Name = item["Product_Type_Name"].ToString();
                p.Product_Barcode = item["Product_Barcode"].ToString();
                p.Supplier_Name = item["Supplier_Name"].ToString();
                p.Product_Remark = item["Product_Remark"].ToString();
                list.Add(p);
            }
            return list;
        }
        /// <summary>
        /// 获取商品信息总数
        /// </summary>
        /// <returns></returns>
        public int CountProduc()
        {
            StringBuilder bulider = new StringBuilder("select COUNT(*) from Product");
            return Convert.ToInt32(helper.ExecScalar(bulider.ToString()));
        }
        /// <summary>
        /// 根据ID获取单个商品信息
        /// </summary>
        /// <returns></returns>
        public List<Product> GetProductByID(int Product_Id)
        {
            StringBuilder bulider = new StringBuilder("select Product_Id,Product_Name,Product_Modelspe,Product_Bprid,Product_Barcode,Product_Remark from Product where Product_Id=@Product_Id");
            SqlParameter[] par={
                new SqlParameter("@Product_Id",SqlDbType.Int,4)
            };
            par[0].Value = Product_Id;
            DataTable table = helper.ExecDataTable(bulider.ToString(),par);
             List<Product> list = new List<Product>();
            foreach (DataRow item in table.Rows)
            {
                Product p = new Product();
                p.Product_Id = Convert.ToInt32(table.Rows[0]["Product_Id"]);
                p.Product_Name = table.Rows[0]["Product_Name"].ToString();
                p.Product_Modelspe = table.Rows[0]["Product_Modelspe"].ToString();
                p.Product_Bprid = Convert.ToDecimal(table.Rows[0]["Product_Bprid"]);
                p.Product_Barcode = table.Rows[0]["Product_Barcode"].ToString();
                p.Product_Remark = table.Rows[0]["Product_Remark"].ToString();
                list.Add(p);
            }
            return list;
        }
        /// <summary>
        /// 新增商品
        /// </summary>
        /// <returns></returns>
        public int AddProduct(Product po)
        {
            StringBuilder bulider = new StringBuilder("insert into Product values(@Product_Name,@Product_Modelspe,@Product_Bprid,@Product_Price,@Product_Type_Id,@Product_Barcode,@Supplier_Id,@Product_Remark)");
            SqlParameter[] par ={
                new SqlParameter("@Product_Name",SqlDbType.VarChar,50),
                new SqlParameter("@Product_Modelspe",SqlDbType.VarChar,50),
                new SqlParameter("@Product_Bprid",SqlDbType.Decimal),
                new SqlParameter("@Product_Price",SqlDbType.Decimal),
                new SqlParameter("@Product_Type_Id",SqlDbType.Int,4),
                new SqlParameter("@Product_Barcode",SqlDbType.VarChar,50),
                new SqlParameter("@Supplier_Id",SqlDbType.Int,4),
                new SqlParameter("@Product_Remark",SqlDbType.VarChar,1000)
            };
            par[0].Value = po.Product_Name;
            par[1].Value = po.Product_Modelspe;
            par[2].Value = po.Product_Bprid;
            par[3].Value = po.Product_Price;
            par[4].Value = po.Product_Type_Id;
            par[5].Value = po.Product_Barcode;
            par[6].Value = po.Supplier_Id;
            par[7].Value = po.Product_Remark;
            return helper.ExecNonquery(bulider.ToString(),par);
        }
        /// <summary>
        /// 根据编号删除商品信息
        /// </summary>
        /// <returns></returns>
        public int DelProductByID(int id)
        {
            StringBuilder bulider = new StringBuilder("delete from Product where Product_Id=@id");
            SqlParameter[] par ={
                new SqlParameter("@id",SqlDbType.Int,4),
            };
            par[0].Value = id;
            return helper.ExecNonquery(bulider.ToString(), par);
        }
        /// <summary>
        /// 根据商品名称获取商品库存信息
        /// </summary>
        /// <returns></returns>
        public string GetProductByName(int WareHose_Id, string Product_Name)
        {
            StringBuilder bulider = new StringBuilder("select Product.Product_Id,Product_Name,Product_Bprid,Product_Modelspe,Product_Barcode,Stock_num,WareHose_Id from Product join Stock on Stock.Product_Id=Product.Product_Id where Product_Name=@Product_Name  and WareHose_Id=@WareHose_Id");
            SqlParameter[] par ={
                new SqlParameter("@Product_Name",SqlDbType.VarChar,40),
                 new SqlParameter("@WareHose_Id",SqlDbType.Int,4)
            };
            par[0].Value = Product_Name;
            par[1].Value = WareHose_Id;
            DataTable table = helper.ExecDataTable(bulider.ToString(), par);
            JavaScriptSerializer jsSerializer = new JavaScriptSerializer();
          List<Dictionary<string, object>> parentRow = new List<Dictionary<string, object>>();
          Dictionary<string, object> childRow;
          foreach (DataRow row in table.Rows)
          {
              childRow = new Dictionary<string, object>();
              foreach (DataColumn col in table.Columns)
              {
                  childRow.Add(col.ColumnName, row[col]);
              }
              parentRow.Add(childRow);
          }
          return jsSerializer.Serialize(parentRow);
        }
    }
}